<?php
/****
 * @time20140912
 * 可以废弃了
 */

defined('SYSPATH') or die('No direct access allowed.');
//计算销量
class Service_daysale2{

	//计算单个sku的销量
	static function count_daysale($date_start,$date_end)
	{
		//国内销售出库
		$type = array(-4,1,3,5,6,9,14,15,16,17,24,43);
		
		//循环次数
		$index=(strtotime($date_end)-strtotime($date_start))/(7*24*3600);
		
		$skus_counts=array();
		
		for($i=0;$i<$index;$i++){
			
			$date_start=date('Y-m-d',strtotime($date_end.'-7 days'));
			$skus_count = DB::select ( array (DB::expr('upper(trim(erp_orders_products.orders_sku))'),'sku'),
					   array (DB::expr ( 'SUM(erp_orders_products.item_count)' ),'count') )
								->from ( 'erp_orders_products' )
								->join('erp_orders')
								->on('erp_orders_products.erp_orders_id', '=','erp_orders.erp_orders_id')
								->where ( 'erp_orders.orders_export_time', '>=', $date_start )
								->where ( 'erp_orders.orders_export_time', '<', $date_end )
								->where ('erp_orders.orders_type', 'in', $type)
								->where(DB::expr('upper(trim(erp_orders_products.orders_sku))'),'!=','')
								->group_by (DB::expr('upper(trim(erp_orders_products.orders_sku))') )
								->execute ( "readonlyerp" )
 								->as_array();
			//var_dump($skus_count);exit;
			$date_end = $date_start;
			$skus_counts[$i]=$skus_count;
		}
			
	//	var_dump($count_sku);exit;
		return $skus_counts;
	}


	//计算全部sku的销量 $days = 7/14/21/28天
	static function count_all_daysale($date_start,$date_end)
	{
		//国内销售出库
		$type = array(-4,1,3,5,6,9,14,15,16,17,24,43);
//		-----------------------------------------kdw--------------------------------------
//		$skus = DB::select(array(DB::expr('upper(erp_products_data.products_sku)'),'sku'))
//			->from('erp_products_data')
//			->execute('readonlyerp')
//			->as_array();

        $skus=Skusystemapi::getAllSku();
//		-----------------------------------------kdw-end----------------------------------
// 			echo "skus:".count($skus)."<br/>";
			$all_skus = array();//全部的SKU
			if(count($skus) >0)
			{
				//全部的SKU
				foreach ($skus as $sku) 
				{
					if(!is_null($sku['sku']) or trim($sku['sku']) !=" "){
						$all_skus[] = trim($sku['sku']);
					}
				}
			
			//获取到所有的sku的销量 0:28-21 1:21-14 2:14-7 3:7-0
			$skus_counts=Service_daysale2::count_daysale($date_start,$date_end);
			
			//存放有销量的sku(也就是28天的时间内有订单的sku)
			$index=(strtotime($date_end)-strtotime($date_start))/(7*24*3600);
			$sale_skus=array();
			
			//存放所有sku的0-7/7-14/14-21/21-28 各时间段的销量
			$skus_sale_arr=array();
			foreach ($skus_counts as $index=>$skus_count){
				
				foreach ($skus_count as $sku_count){
					if(!array_key_exists($sku_count['sku'], $skus_sale_arr)){
						
						$sku_sale_arr=array();
						$sku_sale_arr[$index*7+7]=$sku_count['count'];
						$skus_sale_arr[$sku_count['sku']]=$sku_sale_arr;
						
					}else{
						
						$sku_sale_arrs=$skus_sale_arr[$sku_count['sku']];
						$sku_sale_arrs[$index*7+7]=$sku_count['count'];
						$skus_sale_arr[$sku_count['sku']]=$sku_sale_arrs;
						
					}
					
					//获取在给定的28天时间内有销量的sku
					if(!in_array($sku_count['sku'], $sale_skus)){
						$sale_skus[]=$sku_count['sku'];
					}
				}
			}
			
			//补全有效量的sku但规定时间段内销量为0的销量
			foreach ($skus_sale_arr as $key=>$sku_sale_arr){
				for($i=0;$i<$index;$i++){
					if(!array_key_exists($i*7, $sku_sale_arr)){
						$sku_sale_arr[$i*7]="0";
					}
				}
				
				$skus_sale_arr[$key]=$sku_sale_arr;
			}
			
			//标记PTB001800,测试使用例
			//$sku_day_sale_info=$skus_sale_arr['PTB001800'];
			//$sku_day_sale_info[7]="1000";
		//$sku_day_sale_info[14]="1000";
 			//$sku_day_sale_info[21]="1000";
			//$sku_day_sale_info[28]="1000";
			//$skus_sale_arr['PTB001800']=$sku_day_sale_info;
			
			echo "skus_sale_arr1:".count($skus_sale_arr)."<br/>";
			
			$start1=Service_daysale2::getmicrotime();
			
			//存放28天时间段内没有销量的sku
			$no_sale_skus=array_diff($all_skus, $sale_skus);
			
			//将没有销量的sku放到有销量的sku之中（汇总得到总的sku）
			foreach ($no_sale_skus as $no_sale_sku){
				$skus_sale_arr[$no_sale_sku]=$no_sale_sku;
			}
			
			echo "skus_sale_arr2:".count($skus_sale_arr)."<br/>";
			echo "all_skus:".count($all_skus)."<br/>";
			
			//将sku的销量到数据库表product_attached( 在28天内有销量的)
			Service_daysale2::batch_save_warning($skus_sale_arr);
			$end1=Service_daysale2::getmicrotime();
			echo "time1:".($end1-$start1)."<br/>";
			
		}	
		
	}
	
	//统计代码的执行时间
	static 	function getmicrotime(){
		list($usec, $sec) = explode(" ",microtime());
		return ((float)$usec + (float)$sec);
	}
	
	
	/**
	 * 批量保存预警信息
	 * @param array $warning
	 */
	static function batch_save_warning(array $skus_arr)
	{
		// 		var_dump($skus_arr['PTB001800']);exit();
		// 		echo "<br/>";
	
		if(empty($skus_arr))
		{
			return;
		}
	
		echo "skus:".count($skus_arr)."<br/>";
		//拼接insert/update语句
		$insert="insert into product_attached (sku,daysales7,daysales14,daysales21,daysales28,createtime,updatatime,wave_days,target_time) values";
		$index=7;
		foreach($skus_arr as $sku=>$warning){
				
			//获取 0-7/0-14/0-21/0-28各时间段内所有sku对应的销量
				
			if(is_array($warning)){
				$daysales7=$warning[$index];
				$daysales14=$warning[$index*2]+$daysales7;
				$daysales21=$warning[$index*3]+$daysales14;
				$daysales28=$warning[$index*4]+$daysales21;
	
			}else{
	
				$daysales7=0;
				$daysales14=0;
				$daysales21=0;
				$daysales28=0;
			}
	
			//拼接insert部分语句
			$insert.="('".$sku."',".$daysales7.",".$daysales14.",".$daysales21.",".$daysales28.",'".date('Y-m-d H:i:s')."','00-00-00 00:00:00',3,7),";
				
		}
			
		//去掉sql语句最后一个逗号
		$insert_sql=substr($insert, 0,strlen($insert)-1);
			
		//拼接update部分语句
		$insert_sql.=" on duplicate key update daysales7=values(daysales7),daysales14=values(daysales14),daysales21=values(daysales21),daysales28=values(daysales28),updatatime='".date('Y-m-d H:i:s')."'";
	
		//echo "sql:".$insert_sql."<br/>";
	
		$row_total = DB::query(Database::INSERT,$insert_sql)->execute('readonlyerp');
	
		echo "row_total".count($row_total)."<br/>";
	
		//计算sku的加权销量
		Service_daysale2::count_daysale_weighting();
	}

	//计算加权日均销量
	static function count_daysale_weighting()
	{
		$sql = 'update product_attached set daysales=((daysales7)/7 * 0.5 + (daysales14)/14 * 0.3 + (daysales28)/28 * 0.2)';
		$row_total = DB::query(Database::UPDATE,$sql)->execute('readonlyerp');
		return $row_total;
	}


	//计算订单数量
    /*
     * 不要使用了！
     *
     *
     *
     *
     *
     */
	static function count_big_orders_all($date_start,$date_end)
	{

		$counts  = DB::select()
				->from('product_attached')
				->where('daysales','>',0)
				->execute('readonlyerp')
				->count();

		for ($i=0; $i < ($counts / 100 + 1); $i++) 
		{ 
			$sku = DB::select()
				->from('product_attached')
				->where('daysales','>',0)
				->offset($i * 100)
				->limit(100)
				->execute('readonlyerp')
				->as_array();

				if($sku)
				{
					foreach ($sku as $key => $value) 
					{
						//var_dump($value);exit;
						$big_orders = Service_daysale2::count_big_orders_sku($value,$date_start,$date_end);
						Service_Warningcount::save_warning($big_orders);
					}
				}
		}
	}

	//计算单个SKU大单数量
	static function count_big_orders_sku($sku,$date_start,$date_end)
	{
		// var_dump($sku);exit;
	$big_orders = DB::select(array ('erp_orders_products.orders_sku','sku'),
								array(DB::expr('COUNT(erp_orders_products.erp_orders_id)'),'counts'))
							->from ( 'erp_orders_products' )
							->join ( 'erp_orders' )
							->on( 'erp_orders_products.erp_orders_id', '=', 'erp_orders.erp_orders_id' )
							->where('erp_orders_products.orders_sku','=',$sku['sku'])
							->where('erp_orders_products.item_count','>=',$sku['bigvalues'])
							->where ( 'erp_orders.orders_export_time', '>=', $date_start )
							->where ( 'erp_orders.orders_export_time', '< ', $date_end )
							->group_by('erp_orders_products.orders_sku')
							->execute ( 'readonlyerp' )
							->current();
	$sku['big_orders'] = empty($big_orders)?0:$big_orders['counts'];

	echo 'SKU:'.$sku['sku'];
	echo "bigvalues:".$sku['bigvalues'];
	echo 'big_orders:'.$sku['big_orders']."<br/>";

	return $sku;

		//Service_Warningcount::save_warning($sku);
	}

}